package com.example.goodsmanagementcabinet.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;

import com.blankj.utilcode.utils.SDCardUtils;
import com.example.goodsmanagementcabinet.bean.KeyBean;
import com.example.goodsmanagementcabinet.bean.OverBean;
import com.example.goodsmanagementcabinet.bean.UserBean;

import java.io.File;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class DatabaseManager {
    private static DatabaseManager mInstance;
    private static SQLiteOpenHelper mHelper;

    public synchronized static DatabaseManager getInstance(Context context) {
        if (mInstance == null) {
            mInstance = new DatabaseManager();
            mHelper = DatabaseHelper.getInstance(context);
        }
        return mInstance;
    }

    /**
     * "keyId text," +
     * "keyName  text," +
     * "keyCode  text," +
     * "status text," +
     * "location text," +
     */

    public void insertToKey(KeyBean key) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            ContentValues cv = new ContentValues();
            cv.put("keyId", key.getKeyId());
            cv.put("keyName", key.getKeyName());
            cv.put("keyCode", key.getKeyCode());
            cv.put("status", key.getStatus());
            cv.put("location", key.getLocation());
            db.insert("keys", null, cv);
        }
    }

    public void insertToHandOver(List<OverBean> overBeanList) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            ContentValues cv = new ContentValues();
            for (OverBean over : overBeanList) {
                cv.put("overid", over.getOverid());
                cv.put("date", over.getDate());
                cv.put("handoveruserid", over.getHandoveruserid());
                cv.put("takeoveruserid", over.getTakeoveruserid());
                cv.put("overkeyid", over.getOverkeyid());
                cv.put("status", over.getStatus());
                db.insert("handoverandtakeover", null, cv);
                cv.clear();
            }
        }
    }

    public void updateHandOverState(String jointype, List<OverBean> list) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            for (OverBean over : list) {
                String sql = "update handoverandtakeover set status = '" + jointype + "' where overid = '" + over.getOverid() + "'";
                db.execSQL(sql);
            }
        }
    }

    public List<OverBean> queryOvers(String userid, String status) {
        List<OverBean> mArrayList = new ArrayList<>();
        mArrayList.clear();
        SQLiteDatabase db = mHelper.getReadableDatabase();
        if (db.isOpen()) {
            String sql = "select distinct A.* from handoverandtakeover A where takeoveruserid = '" + userid + "' and status = '" + status + "'";
            Cursor cursor = db.rawQuery(sql, null);// 查询并获得游标
            while (cursor.moveToNext()) {
                OverBean over = new OverBean();
                over.setOverid(cursor.getString(cursor.getColumnIndex("overid")));
                over.setTakeoveruserid(cursor.getString(cursor.getColumnIndex("takeoveruserid")));
                over.setHandoveruserid(cursor.getString(cursor.getColumnIndex("handoveruserid")));
                over.setOverkeyid(cursor.getString(cursor.getColumnIndex("overkeyid")));
                over.setStatus(cursor.getString(cursor.getColumnIndex("status")));
                over.setDate(cursor.getString(cursor.getColumnIndex("date")));
                mArrayList.add(over);
            }
            cursor.close();
        }
        return mArrayList;
    }

    public void insertToKeyUser(String userid, String keyId) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            ContentValues cv = new ContentValues();
            cv.put("keyid", keyId);
            cv.put("userid", userid);
            db.insert("keyusers", null, cv);
        }
    }

    public void deleteKeyUserByKeyidAndUserId(String userId, String keyId) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            String sql = "delete from keyusers where userid = '" + userId + "' and keyid = '" + keyId + "'";
            db.execSQL(sql);
        }
    }

    public List<KeyBean> queryAllKey() {
        List<KeyBean> mArrayList = new ArrayList<>();
        List<String> location = new ArrayList<>();
        for (int i = 33; i > 0; i--) {
            location.add(i + "");
        }
        mArrayList.clear();
        SQLiteDatabase db = mHelper.getReadableDatabase();
        if (db.isOpen()) {
            String sql = "select * from keys";
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                KeyBean key = new KeyBean();
                key.setKeyId(cursor.getString(cursor.getColumnIndex("keyId")));
                key.setKeyName(cursor.getString(cursor.getColumnIndex("keyName")));
                key.setKeyCode(cursor.getString(cursor.getColumnIndex("keyCode")));
                key.setLocation(cursor.getString(cursor.getColumnIndex("location")));
                key.setStatus(cursor.getString(cursor.getColumnIndex("status")));
                if (key.getStatus().equals("1") || key.getStatus().equals("2")) {
                    location.remove(key.getLocation());
                    mArrayList.add(key);
                }
            }
            cursor.close();
        }
        //判断当前是否已存满
        for (String lo : location) {
            KeyBean key = new KeyBean();
            key.setStatus("-6");
            key.setLocation(lo);
            mArrayList.add(key);
        }
        Collections.sort(mArrayList);
        return mArrayList;
    }

    public void deleteKeyByKeyId(String keyid) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            String sql = "delete from keys where keyId = '" + keyid + "'";
            db.execSQL(sql);
        }
    }

    public void deleteKeyUserByKeyid(String keyid) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            String sql = "delete from keyusers where keyid = '" + keyid + "'";
            db.execSQL(sql);
        }
    }

    /**
     * "deptName text," +                 //人员编号
     * "nickname  text," +
     * "deptId  text," +                  // 姓名
     * "userId  text," +                  // 男、女
     * "username text," +
     * "image text," +
     */

    public List<UserBean> queryUsers() {
        List<UserBean> mArrayList = new ArrayList<>();
        mArrayList.clear();
        SQLiteDatabase db = mHelper.getReadableDatabase();
        if (db.isOpen()) {
            String sql = "select distinct A.*, B.userid from users A left join fingers B on A.userId=B.userid";
            Cursor cursor = db.rawQuery(sql, null);// 查询并获得游标
            while (cursor.moveToNext()) {
                UserBean userBean = new UserBean();
                userBean.setNickname(cursor.getString(cursor.getColumnIndex("nickname")));
                userBean.setUserId(cursor.getString(cursor.getColumnIndex("userId")));
                userBean.setPassWord(cursor.getString(cursor.getColumnIndex("username")));
                if (!TextUtils.isEmpty(cursor.getString(cursor.getColumnIndex("userid")))) {
                    userBean.setStoreAt("1");
                } else {
                    userBean.setStoreAt("0");
                }
                mArrayList.add(userBean);
            }
            cursor.close();
        }
        return mArrayList;
    }

    public List<UserBean> queryUsers(String userid) {
        List<UserBean> mArrayList = new ArrayList<>();
        mArrayList.clear();
        SQLiteDatabase db = mHelper.getReadableDatabase();
        if (db.isOpen()) {
            String sql = "select distinct A.* from users A where userId !='" + userid + "'";
            Cursor cursor = db.rawQuery(sql, null);// 查询并获得游标
            while (cursor.moveToNext()) {
                UserBean userBean = new UserBean();
                userBean.setNickname(cursor.getString(cursor.getColumnIndex("nickname")));
                userBean.setUserId(cursor.getString(cursor.getColumnIndex("userId")));
                userBean.setPassWord(cursor.getString(cursor.getColumnIndex("username")));
                mArrayList.add(userBean);
            }
            cursor.close();
        }
        return mArrayList;
    }

    public void deleteUserInfo(UserBean userBean) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            String sql = "delete from users where userId = '" + userBean.getUserId() + "'";
            db.execSQL(sql);
            sql = "delete from fingers where userid = '" + userBean.getUserId() + "'";
            db.execSQL(sql);
        }
    }

    public void deleteKeyUserByUserId(String userId) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            String sql = "delete from keyusers where userid = '" + userId + "'";
            db.execSQL(sql);
        }
    }

    public UserBean queryUserByusrName(String userid) {
        UserBean userBean = null;
        SQLiteDatabase db = mHelper.getReadableDatabase();
        if (db.isOpen()) {
            String sql = "select * from users where userId = '" + userid + "'";
            Cursor cursor = db.rawQuery(sql, null);// 查询并获得游标
            while (cursor.moveToNext()) {
                userBean = new UserBean();
                userBean.setNickname(cursor.getString(cursor.getColumnIndex("nickname")));
                userBean.setUserId(cursor.getString(cursor.getColumnIndex("userId")));
                userBean.setPassWord(cursor.getString(cursor.getColumnIndex("username")));
            }
            cursor.close();
        }
        return userBean;
    }


    public void insertToUser(UserBean user) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            ContentValues cv = new ContentValues();// 实例化一个ContentValues用来装载待插入的数据cv.put()
            cv.put("userId", user.getUserId());
            cv.put("username", user.getPassWord());
            cv.put("nickname", user.getNickname());
            db.insert("users", null, cv);
        }
    }

    public List<KeyBean> queryKeyByUserId(String userid, String status) {
        List<KeyBean> mArrayList = new ArrayList<>();
        mArrayList.clear();
        SQLiteDatabase db = mHelper.getReadableDatabase();
        if (db.isOpen()) {
            String sql = "select a.* from keys a,keyusers b where status = '" + status + "' and a.keyId = b.keyid and b.userid = '" + userid + "'";
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                KeyBean key = new KeyBean();
                // key = new KeyBean();
                key.setKeyId(cursor.getString(cursor.getColumnIndex("keyId")));
                key.setKeyName(cursor.getString(cursor.getColumnIndex("keyName")));
                key.setKeyCode(cursor.getString(cursor.getColumnIndex("keyCode")));
                key.setStatus(cursor.getString(cursor.getColumnIndex("status")));
                key.setLocation(cursor.getString(cursor.getColumnIndex("location")));
                mArrayList.add(key);
            }
            cursor.close();
        }
        return mArrayList;
    }

    public KeyBean queryKeyByKeyId(String keyid) {
        KeyBean key = null;
        SQLiteDatabase db = mHelper.getReadableDatabase();
        if (db.isOpen()) {
            String sql = "select a.* from keys a where a.keyId = '" + keyid + "'";
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                key = new KeyBean();
                key.setKeyId(cursor.getString(cursor.getColumnIndex("keyId")));
                key.setKeyName(cursor.getString(cursor.getColumnIndex("keyName")));
                key.setKeyCode(cursor.getString(cursor.getColumnIndex("keyCode")));
                key.setStatus(cursor.getString(cursor.getColumnIndex("status")));
                key.setLocation(cursor.getString(cursor.getColumnIndex("location")));
            }
            cursor.close();
        }
        return key;
    }

    public List<KeyBean> queryKeyByStatus(String status) {
        List<KeyBean> mArrayList = new ArrayList<>();
        mArrayList.clear();
        SQLiteDatabase db = mHelper.getReadableDatabase();
        if (db.isOpen()) {
            String sql = "select * from keys where status = '" + status + "'";
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                KeyBean key = new KeyBean();
                // key = new KeyBean();
                key.setKeyId(cursor.getString(cursor.getColumnIndex("keyId")));
                key.setKeyName(cursor.getString(cursor.getColumnIndex("keyName")));
                key.setKeyCode(cursor.getString(cursor.getColumnIndex("keyCode")));
                key.setStatus(cursor.getString(cursor.getColumnIndex("status")));
                key.setLocation(cursor.getString(cursor.getColumnIndex("location")));
                mArrayList.add(key);
            }
            cursor.close();
        }
        return mArrayList;
    }

    public void updateKeyState(String jointype, List<KeyBean> list) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            for (KeyBean key : list) {
                String sql = "update keys set status = '" + jointype + "' where keyCode = '" + key.getKeyCode() + "'";
                db.execSQL(sql);
            }
        }
    }

    public void updateKeyState(String jointype, KeyBean key) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            String sql = "update keys set status = '" + jointype + "' where keyCode = '" + key.getKeyCode() + "'";
            db.execSQL(sql);
        }
    }

    public List<UserBean> queryUserByKeyId(String keyId) {
        List<UserBean> userList = new ArrayList<>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            String sql = "select a.* from users a,keyusers b where b.keyid = '" + keyId + "' and b.userid = a.userId";
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                UserBean user = new UserBean();
                // key = new KeyBean();
                user.setUserId(cursor.getString(cursor.getColumnIndex("userId")));
                user.setNickname(cursor.getString(cursor.getColumnIndex("nickname")));
                user.setPassWord(cursor.getString(cursor.getColumnIndex("username")));
                userList.add(user);
            }
        }
        return userList;
    }

    private static final String sqls = "insert or replace into fingers (userid, type, finger)  VALUES ('%s','%s','%s');";

    public void insertToFingers(String userid, String fingertype, String finger) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            String rawSql = String.format(sqls, userid, fingertype, finger);
            db.execSQL(rawSql);
        }
    }

    public String queryFingerByUserId(String userId) {
        String a = "";
        SQLiteDatabase db = mHelper.getReadableDatabase();
        if (db.isOpen()) {
            String sql = "select * from fingers where userid = '" + userId + "'";
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                a = cursor.getString(cursor.getColumnIndex("finger"));
            }
            cursor.close();
        }
        return a;

    }

    public void deleteFingerByUserid(String userId) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        if (db.isOpen()) {
            String sql = "delete from fingers where userid = '" + userId + "'";
            db.execSQL(sql);
        }
    }
}
